允许进行选择的多列求和 EXCEL(25)_EXCEL必备工具箱_EXCEL_工具

EXCEL中容易被忽略的功能(25):允许进行选择的多列求和

我们在日常工作中，通常会遇到下面的情况，比如按月计算利润指标的当年累计，如下图：

如果按照常规的办法，只能在每个月份后都加一列本年累计，不但公式繁琐，表格过长，也非常不简洁，又没有办法，只加一列当年累计，然后选择月份，就自动计算出从年初到这个月份的当年累计呢？

当然有办法，而且不用工具，不用编程，不用宏，只要简单的excel自带函数就可以实现：

可以看到：

只要选择了月份（假设是X这个变量)，标题就会显示1-X月累计，该列数据也会自动变；而且即使后面几个月没有数据，也不影响计算。

实现过程：

1、通过数据有效性，实现列选择：

（1）选定用于存放月份数据的单元格，比如本例中“请选择月份：”后面那个单元格O2

（2）点击数据菜单，再点“有效性...”（excel2007/2010中在功能区ribbon中选择）

2、进行格式设置

进行数据有效性设置后，选择后只会显示数字，不会显示某某月，这时候进行自定义格式就可以了：

3、O3单元格要显示1-X月累计，简单设置一下公式就可以了：

="1-"&O2&"月累计"

4、O4单元格的公式为：

有两种公式：

第一种公式： =SUM(OFFSET(B4,0,0,1,$O$2))

第二种公式： =SUM(INDIRECT(ADDRESS(ROW(B4),2) & ":"& ADDRESS(ROW(B4),$O$2+1)))

然后拖动复制到下面各列就可以了。

第一种公式详细说明：

第一种公式非常简单，方便易懂，建议大家使用。

(1) 公式的主体是 =sum(求和区域)

求和区域是以B4单元格为起点进行偏移形成的，当前状态下$O$2为4，那么OFFSET(B4,0,0,1,$O$2)的含义就是，以B4单元格为起点，偏移了0行，0列，也就是说基准点还是B4,偏移后的区域是以B4为该区域左上角第一个单元格，这个区域的大小是1行，4列。

第二种公式相对较难以理解，通常用于向人展示技术水平，技术控们可以用一下：

(1) 公式的主体是 =sum(求和区域)

这个区域是从B2单元格到指定月份对应的列的第4行数据

(2)为了实现区域地址可变，就只能先设置一个文本型的地址，然后用INDIRECT函数转换成sum函数能够识别的Range对象

INDIRECT(文本型地址）

(3)我们来看这个文本型地址：

ADDRESS(ROW(B4),2)& ":" & ADDRESS(ROW(B4),$O$2+1)

它分为冒号前后的两部分，也就是从ADDRESS(ROW(B4),2)到ADDRESS(ROW(B4),$O$2+1)这么一个地址

我们先看前面那个地址：ADDRESS(ROW(B4),2)

ADDRESS(行，列），表示指定行列的单元格地址，比如ADDRESS(2，3），就表示2行3列的地址，则结果为"C2"

这里就表示B4单元格所在的行，也就是4行，对应第二列，那么，ADDRESS(ROW(B4),2)的结果就是"B4",

Row()函数，是获取指定单元格的行号，ROW(B4)就是获取B4单元格的行号，就是4

(有朋友可能会疑惑，那直接写B4就可以了，为什么写这么复杂呢，当然是为了向下拖动复制时能够自动改变行号）

我们再看后面那个地址：ADDRESS(ROW(B4),$O$2+1)

我想逗号前面，你已经明白了，逗号后面$O$2+1代表列，O2单元格当前显示的是4月，实际值是4，4+1=5，也就是说ADDRESS(ROW(B4),$O$2+1)的当前值是ADDRESS(4,5),即："E4"

之所以是$O$2，而不是O2，这是excel绝对引用表示方式，在行列前面加上美元符号后拖动复制时不发生变化，确保下面每个公式都指向O2单元格。

经过上面的解释，现在看这个公式=SUM(INDIRECT(ADDRESS(ROW(B4),2)& ":" &ADDRESS(ROW(B4),$O$2+1)))就非常一目了然了。

在O2单元格等于4时，=sum(B4:E4)

在O2单元格等于6时，=sum(B4:G4)

只要简单选择一下O2单元格的数据，就可以立即改变当年累计的截止月份了。